package cn.conac.rc.ofs.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.ofs.entity.OrganizationEntity;

@Repository
public interface OrganizationRepository extends GenericDao<OrganizationEntity, Integer> {

	/**
	 * 根据指定数量获取最近变更的三定信息（App用）
	 * @param num
	 * @return
	 */
	@Query(value = "SELECT org.* FROM ofs_organization org, ofs_org_status sts WHERE org.base_id = sts.id and org.status = '0' and org.area_code like ?2 and rownum <= ?1 ORDER BY sts.update_time DESC", nativeQuery = true)
	List<OrganizationEntity> findRecentOrgsByNum(@Param("num") int num,@Param("areaCode") String areaCode);

	/**
	 * 统计行政机关数量
	 * @param areaCode
	 * @return 删除数量
	 */
	@Query("select count(*) from OrganizationEntity a where a.type='1' and a.status='0' and a.areaCode like:areaCode")
	int countXZJG(@Param("areaCode") String areaCode);
	
	/**
	 * 统计事业单位数量
	 * @param areaCode
	 * @return 删除数量
	 */
	@Query("select count(*) from OrganizationEntity a where a.type='2' and a.status='0' and a.areaCode like:areaCode")
	int countSYDW(@Param("areaCode") String areaCode);
	
	/**
	 * 获取没有关联职能职责的内设机构的部门
	 * @return
	 */
	@Query(value = "select distinct oo.*  from ofs_organization oo  left join ofs_organization_base oob    on oo.BASE_ID = oob.id  left join ofs_org_status oos    on oos.id = oob.id inner join ofs_department od    on oo.BASE_ID = od.org_id   and ((oob.type = 1 and od.DEPT_FUNC_TYPE = '01') or       (oob.type = 2 and od.DEPT_FUNC_TYPE = '11')) where od.ID not in       (select distinct odd.DEPART_ID from ofs_depart_duty odd)  and oob.OWN_SYS is not null and oo.BASE_ID is not null  and oob.area_id is not null and oos.status = '03' and oos.update_type <> '50' and oos.is_history = 0 ", nativeQuery = true)
	List<OrganizationEntity> findOrgIdWithNoDeptList();
	
	
	/**
	 * 获取没有关联内设的职能职责的部门
	 * @return
	 */
	@Query(value = "select distinct oo.* from ofs_organization oo left join ofs_organization_base oob on oo.BASE_ID=oob.id left join ofs_org_status oos on oos.id=oob.id inner join ofs_duty od on oo.BASE_ID=od.org_id left join ofs_depart_duty odd on od.ID = odd.duty_id where odd.depart_id is null and oob.own_sys is not null and oo.BASE_ID is not null and oob.area_id is not null and oos.status = '03' and oos.update_type <> '50' and oos.is_history = 0 ", nativeQuery = true)
	List<OrganizationEntity> findOrgIdWithNoDutyList();
	
	/**
	 * 获取没有关联权责的职能职责的部门
	 * @return
	 */
	@Query(value = "select distinct * from (select oo_2.id, oo_2.base_id, oo_2.parent_id, oo_2.name, oo_2.area_code od_2.DUTY_CODE from ofs_organization oo_2 left join ofs_organization_base oob_2 on oo_2.BASE_ID = oob_2.id left join ofs_org_status oos_2 on oos_2.id = oob_2.id INNER JOIN ofs_duty od_2 ON od_2.org_id = oo_2.base_id where oob_2.OWN_SYS is not null and oo_2.BASE_ID is not null and oob_2.area_id is not null and oos_2.status = '03' and oos_2.update_type <> '50' and oos_2.is_history = 0  minus  select distinct oo.id, oo.base_id, oo.parent_id, oo.name, oo.area_code, aid.duty_code from ofs_organization oo left join ofs_organization_base oob on oo.BASE_ID = oob.id left join ofs_org_status oos on oos.id = oob.id LEFT JOIN ael_items_info aii ON oo.id = aii.OWN_ORG_ID INNER JOIN ael_items_duty aid ON aii.id = aid.ITEM_ID AND aid.DUTY_CODE IS NOT NULL LEFT JOIN ofs_duty od ON od.DUTY_CODE = aid.DUTY_CODE where oob.OWN_SYS is not null and oo.BASE_ID is not null and oob.area_id is not null and oos.status = '03' and oos.update_type <> '50' and oos.is_history = 0) ", nativeQuery = true)
	List<OrganizationEntity> findOrgIdWithNoItemList();
	
	
	/**
	 * 获取没有网上名称的部门
	 * @return
	 */
	@Query(value = "select oo.* from ofs_organization oo left join ofs_organization_base oob on oo.BASE_ID=oob.id left join ofs_org_status oos on oos.id=oob.id left join OFS_IMPORT_ORG_INFO oioi on oob.name=oioi.ORG_NAME where oob.OWN_SYS is not null  and  oo.BASE_ID is not null and oob.area_id is not null and oos.status='03' and  oos.update_type<>'50' and oos.is_history=0 and oioi.NET_NAME is null ", nativeQuery = true)
	List<OrganizationEntity> findOrgIdWithNoOLNameList();
	
	
	/**
	 * 根据parentID获取下设机构的列表
	 * @return
	 */
	List<OrganizationEntity> findByParentIdAndTypeAndStatus(@Param("parentId") Integer parentId, @Param("type") String type, @Param("status") String status);
	
	/**
	 * 保存场合根据部门名称检索对应信息
	 * @param name
	 * @return Organization
	 */
	OrganizationEntity findByNameAndAreaCodeAndStatus(@Param("name") String name, @Param("areaCode") String areaCode,@Param("status") String status);
	
	
	/**
	 * 根据baseId检索部门库信息
	 * @param baseId
	 * @return Organization
	 */
	OrganizationEntity findByBaseId(@Param("baseId") Integer baseId);
	
	/**
	 * 更新场合根据部门名称检索对应信息
	 * @param name
	 * @param areaCode
	 * @return Organization
	 */
	@Query(value = "select org.* from ofs_organization org where org.name=:name and org.area_code=:areaCode and org.id !=:orgId and org.status = '0' ", nativeQuery = true)
	OrganizationEntity findByNameAndAreaCodeUpdate(@Param("name") String name, @Param("areaCode") String areaCode,@Param("orgId") Integer orgId);
	
	/**
	 * 根据userId查询部门库信息
	 * @param userId
	 * @return
	 */
	@Query(value = "select org.* from icp_user_ext usere, ofs_organization  org, icp_user user_info where user_info.is_deleted=0 and user_info.user_id=usere.user_id and usere.org_id=org.id   and  usere.user_id=:userId ", nativeQuery = true)
	OrganizationEntity  findOrgInfoByUserId(@Param("userId") Integer userId);
	
	
	/**
	 * 根据parentID获取下设机构的列表
	 * @return
	 */
	@Query(value = "select org.* from ofs_organization org where org.parent_id=:parentId and ( org.status='0' or org.status='1' ) ", nativeQuery = true)
	List<OrganizationEntity> findOrgInfoByParentId(@Param("parentId") Integer parentId);
	
	/**
	
	 * 更新部门库的hasChild字段
	 * @param parentId
	 * @return 更新数量
	 */
	@Query(value = "update ofs_organization a set a.has_child= 1 where exists (select * from ofs_organization c where c.parent_id =:parentId and c.status in ('0','1')) and a.id =:parentId" , nativeQuery = true)
	@Modifying
	int updateHasChildByPid(@Param("parentId") Integer parentId);
	
	/**
	 * 更新部门库的hasChild字段
	 * @param parentId
	 * @return 更新数量
	 */
	@Query(value = "update ofs_organization a set a.has_child= 0 where not exists (select * from ofs_organization c where c.parent_id =:parentId and c.status in ('0','1')) and a.id =:parentId" , nativeQuery = true)
	@Modifying
	int updateHasChildByOriginPid(@Param("parentId") Integer parentId);
}
